﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using System.Data.SqlClient;
using System.Data;

namespace Workflows.Components.ETL
{
    /// <summary></summary>
    public class OracleBulkLoadWorker:IWorker
    {
        private Thread _WorkerThread;
        private OracleBulkLoadInstruction _Instruction;
        private OracleBulkLoadOutput _Output;
        private RunContext _Context;
        private Guid _TicketID;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }
        /// <summary></summary>
        public Thread WorkerThread
        {
            get { return _WorkerThread; }
        }
        /// <summary></summary>
        public InstructionBase Instruction
        {
            get { return _Instruction; }
        }
        /// <summary></summary>
        public ResultBase Output
        {
            get { return _Output; }
        }
        /// <summary></summary>
        public Type ComponentType
        {
            get { return typeof(OracleBulkLoadComponent); }
        }
        /// <summary></summary>
        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }
        /// <summary></summary>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this._Instruction = (OracleBulkLoadInstruction) request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this._Instruction = (OracleBulkLoadInstruction)request;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void DoWork()
        {
            if(this.WorkerStartedEvent !=null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this._Instruction);
            }

            this._WorkerThread = Thread.CurrentThread;
            this.RunBulkLoad();
        }
        /// <summary></summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this._Instruction);
            }

            this._WorkerThread = new Thread(new ThreadStart(this.RunBulkLoad));
            this._WorkerThread.Start();
        }
        /// <summary></summary>
        public void CancelWork()
        {
            if(this._WorkerThread !=null && this._WorkerThread.IsAlive)
            {
                this._WorkerThread.Abort();
                this._WorkerThread.Join();

                if(this.WorkerCancelledEvent !=null)
                {
                    this.WorkerCancelledEvent(this._TicketID, this.GetType(), this._Instruction);
                }
            }
        }

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        #region work
        private void RunBulkLoad()
        {
            try
            {
                if (!string.IsNullOrEmpty(this._Instruction.SrcConnStr) &&
                    !string.IsNullOrEmpty(this._Instruction.SelectSqlStatement))
                {
                    this.CreateSourceDataFile(this._Instruction.SrcConnStr, this._Instruction.SelectSqlStatement,
                        this._Instruction.SrcDataFilePath, false, this._Instruction.FieldDelimiter);
                }

                string dataFolderPath = Path.GetDirectoryName(this._Instruction.SrcDataFilePath);
                string utilityFolderPath = Path.GetDirectoryName(this._Instruction.SqlLoaderProgramFilePath);

                string logFilePath =
                    Path.Combine(utilityFolderPath,
                                 Path.GetFileNameWithoutExtension(this._Instruction.SrcDataFilePath) + ".log");
                if(File.Exists(logFilePath))
                {
                    File.Delete(logFilePath);
                }
                string errorFilePath =
                    Path.Combine(utilityFolderPath,
                                 Path.GetFileNameWithoutExtension(this._Instruction.SrcDataFilePath) + ".bad");
                if(File.Exists(errorFilePath))
                {
                    File.Delete(errorFilePath);
                }
                
                string ctlFilePath =
                    Path.Combine(utilityFolderPath,
                                 Path.GetFileNameWithoutExtension(this._Instruction.SrcDataFilePath) + ".dtl");
                if(File.Exists(ctlFilePath))
                {
                    File.Delete(ctlFilePath);
                }
                this.CreateBulkLoadControlFile(ctlFilePath, this._Instruction.TruncateTableBeforeInsert);

                Process proc=new Process();
                ProcessStartInfo startInfo=new ProcessStartInfo(this._Instruction.SqlLoaderProgramFilePath);
                
                Regex dsRegex=new Regex("data\\s+source\\s*=\\s*([^;\\s]+)",RegexOptions.IgnoreCase);
                Match dsMatch = dsRegex.Match(this._Instruction.ConnectionString);
                string serverName = dsMatch.Groups[1].Value;
                Regex uidRegex = new Regex("user\\s+id\\s*=\\s*([^;\\s]+)|uid\\s*=\\s*([^;\\s]+)", RegexOptions.IgnoreCase);
                Match uidMatch = uidRegex.Match(this._Instruction.ConnectionString);
                string uid = uidMatch.Groups[1].Value;
                Regex pwdRegex = new Regex("password\\s*=\\s*([^;\\s]+)|pwd\\s*=\\s*([^;\\s]+)", RegexOptions.IgnoreCase);
                Match pwdMatch = pwdRegex.Match(this._Instruction.ConnectionString);
                string pwd = pwdMatch.Groups[1].Value;
                string args =
                    string.Format("{0}/{1}@{2} control=\"{3}\"", uid, pwd, serverName,
                                  Path.GetFileName(ctlFilePath));
                startInfo.Arguments = args;
                // startInfo.RedirectStandardOutput = true;
                startInfo.RedirectStandardError = true;
                startInfo.WindowStyle = ProcessWindowStyle.Normal;
                startInfo.UseShellExecute = false;
                startInfo.WorkingDirectory = utilityFolderPath;
                proc.StartInfo = startInfo;
                proc.Start();
                
                // StreamReader outputReader = proc.StandardOutput;
                // string output = outputReader.ReadToEnd();
                // outputReader.Close();
                // Console.WriteLine(output);
                StreamReader errorReader = proc.StandardError;
                string error = errorReader.ReadToEnd();
                // errorReader.Close();
                if(!string.IsNullOrEmpty(error))
                {
                    if(this.WorkerErredEvent !=null)
                    {
                        this.WorkerErredEvent(this._TicketID, this.GetType(), this._Instruction, error,true);
                    }
                }
                proc.WaitForExit();
                string tgtCtlFilePath = Path.Combine(dataFolderPath, Path.GetFileName(ctlFilePath));
                if(File.Exists(tgtCtlFilePath))
                {
                    File.Delete(tgtCtlFilePath);
                }
                File.Move(ctlFilePath,tgtCtlFilePath);
                string tgtLogFilePath = string.Empty;
                if(File.Exists(logFilePath))
                {
                    tgtLogFilePath = Path.Combine(dataFolderPath, Path.GetFileName(logFilePath));
                    if(File.Exists(tgtLogFilePath))
                    {
                        File.Delete(tgtLogFilePath);
                    }
                    File.Move(logFilePath,tgtLogFilePath);
                }
                string tgtBadFilePath = string.Empty;
                if(File.Exists(errorFilePath))
                {
                    tgtBadFilePath = Path.Combine(dataFolderPath, Path.GetFileName(errorFilePath));
                    if(File.Exists(tgtBadFilePath))
                    {
                        File.Delete(tgtBadFilePath);
                    }
                    File.Move(errorFilePath,tgtBadFilePath);
                }

                this._Output =
                    new OracleBulkLoadOutput(this._Instruction.OwnerComponentID, this._TicketID, 0, 0, tgtLogFilePath,
                                             tgtBadFilePath);
                if(this.WorkerFinishedEvent !=null)
                {
                    this.WorkerFinishedEvent(this._TicketID, this.GetType(), this._Instruction, this._Output);
                }
            }
            catch(Exception ex)
            {
                if(this.WorkerErredEvent !=null)
                {
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this._Instruction, ex.ToString(),true);
                }
            }
        }

        private void CreateSourceDataFile(
            string srcConnStr, string selectSql, string srcDataFilePath,
            bool fixFieldSize, FieldDelimiter delimiter)
        {
            SqlConnection conn = new SqlConnection(srcConnStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = selectSql;
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter daSelect = new SqlDataAdapter(cmd);
                DataSet dsSelect = new DataSet();
                daSelect.Fill(dsSelect);
                DataTable dtSelect = dsSelect.Tables[0];
                if (File.Exists(srcDataFilePath))
                {
                    File.Delete(srcDataFilePath);
                }
                string delimiterStr = "\t";
                switch (delimiter)
                {
                    case FieldDelimiter.Comma:
                        delimiterStr = ",";
                        break;
                    case FieldDelimiter.None:
                        delimiterStr = string.Empty;
                        break;
                    case FieldDelimiter.Space:
                        delimiterStr = " ";
                        break;
                    case FieldDelimiter.Tab:
                        delimiterStr = "\t";
                        break;
                    case FieldDelimiter.VerticalBar:
                        delimiterStr = "|";
                        break;
                    default:
                        break;
                }
                StreamWriter writer = new StreamWriter(srcDataFilePath);
                StringBuilder line = new StringBuilder();
                //for(int i=0;i<dtSelect.Columns.Count;i++)
                //{
                //    line.Append(dtSelect.Columns[i].ColumnName);
                //    if (i < dtSelect.Columns.Count - 1)
                //        line.Append(delimiterStr);
                //}
                //writer.WriteLine(line.ToString());

                foreach(DataRow dr in dtSelect.Rows)
                {
                    line=new StringBuilder();
                    for(int i=0;i<dtSelect.Columns.Count;i++)
                    {
                        if (dr[i] == null || dr[i]==DBNull.Value)
                        {
                            line.Append(string.Empty);
                        }
                        else
                        {
                            // date time

                            if (dtSelect.Columns[i].DataType == typeof(DateTime))
                            {
                                try
                                {
                                    DateTime dateVal = (DateTime)dr[i];
                                    string dd = string.Format("{0:dd}", dateVal);
                                    string mmm = string.Format("{0:MMM}", dateVal);
                                    string yyyy = string.Format("{0:yyyy}", dateVal);
                                    string dateValue =
                                        string.Format("{0} {1} {2}",
                                            dd, mmm, yyyy);
                                    line.Append(dateValue);
                                }
                                catch (Exception)
                                {
                                    line.Append(string.Empty);
                                }
                            }
                            else
                            {
                                line.Append(dr[i].ToString());
                            }
                        }

                        if (i < dtSelect.Columns.Count - 1)
                        {
                            line.Append(delimiterStr);
                        }
                    }
                    writer.WriteLine(line.ToString());
                }

                writer.Flush();
                writer.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to create soruce data file: " + ex.ToString());
            }
            finally
            {
                conn.Close();
            }

        }

        private void CreateBulkLoadControlFile(string controlFilePath, bool truncateBeforeLoad)
        {
            StreamWriter writer = new StreamWriter(controlFilePath);
            writer.WriteLine("LOAD DATA");
            writer.WriteLine("INFILE '{0}'",this._Instruction.SrcDataFilePath);
            if (truncateBeforeLoad)
            {
                writer.WriteLine("TRUNCATE INTO TABLE {0}", this._Instruction.TableName);
            }
            else
            {
                writer.WriteLine("APPEND INTO TABLE {0}", this._Instruction.TableName);
            }
            // delimiter
            string delimiter = "";
            switch(this._Instruction.FieldDelimiter)
            {
                case FieldDelimiter.Tab:
                    delimiter = "X'9'";
                    break;
                case FieldDelimiter.Comma:
                    delimiter = "','";
                    break;
                case FieldDelimiter.Space:
                    delimiter = "' '";
                    break;
                case FieldDelimiter.VerticalBar:
                    delimiter = "'|'";
                    break;
                default:
                    break;
            }
            writer.WriteLine("FIELDS TERMINATED BY {0}", delimiter);
            writer.WriteLine("TRAILING NULLCOLS");
            writer.Write("(");
            TableColumn[] cols =
                DbUtil.GetTableColumns(DataProviderType.Oracle, this._Instruction.ConnectionString, string.Empty,
                                       this._Instruction.TableName);
            if (cols == null || cols.Length == 0)
            {
                // create target table 
                TableColumn[] srcCols = DbUtil.GetTableColumns(DataProviderType.MSSQL, this._Instruction.SrcConnStr,
                    this._Instruction.SelectSqlStatement);
                this.CreateOracleTable(this._Instruction.ConnectionString, string.Empty, this._Instruction.TableName, srcCols);
                cols = srcCols;
            }
            Dictionary<string,string> oracleFieldDbTypeMappings=new Dictionary<string, string>();

            
            for(int i=0;i<cols.Length;i++)
            {
                TableColumn col = cols[i];
                string colName = col.ColumnName;
                FieldDataType fieldDataType=new FieldDataType();
                fieldDataType.DataType = col.DbType;
                fieldDataType.Nullible = col.AllowNull;
                fieldDataType.Precision = col.Precision;
                fieldDataType.Provider = DataProviderType.Oracle;
                fieldDataType.Scale = col.Scale;
                fieldDataType.Size = col.Size;
                string oracleDbTypeName = FieldDataType.ToOracleFieldDataTypeString(fieldDataType);
                // remove size
                Regex dataTypeRegex = new Regex("(\\S+)\\s*\\(\\s*\\d+\\s*\\)|\\S+\\s*\\(\\s*\\d+\\s*,\\s*\\d+\\s*\\)");
                Match dataTypeMatch = dataTypeRegex.Match(oracleDbTypeName);
                if(dataTypeMatch.Success)
                {
                    oracleDbTypeName = dataTypeMatch.Groups[1].Value;
                }
                oracleFieldDbTypeMappings.Add(colName,oracleDbTypeName);
                
            }

            for (int i = 0; i < cols.Length; i++)
            {
                string colName = cols[i].ColumnName;
                string oracleDbTypeName = oracleFieldDbTypeMappings[colName];
                colName = "\"" + colName + "\"";
                //if (cols[i].DbType == SqlDbType.DateTime)
                //{
                //    colName = colName + " DATE \"YYYY-MM-DD HH24:MI:SS\"";
                //}
                //else if (cols[i].DbType == SqlDbType.Date)
                //{
                //    colName = colName + " DATE \"YYYY-MM-DD\"";
                //}
                if (i < cols.Length - 1)
                {
                    writer.WriteLine(colName + ",");
                }
                else
                {
                    writer.WriteLine(colName);
                }
            }
            //for(int i=0;i<this._Instruction.TableFields.Count;i++)
            //{
                
            //}
            writer.Write(")");
            writer.Flush();
            writer.Close();
        }

        private void CreateOracleTable(string oraConnStr, string oraSchema, string oraTblName, TableColumn[] srcCols)
        {
            DbUtil.NewTable(DataProviderType.Oracle, oraConnStr, oraSchema, oraTblName, srcCols);
        }
        #endregion
    }
}
